package avicit.cbb.businessclass.fondsmanage.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import avicit.cbb.businessclass.fondsmanage.domain.CbbFondsDb;
import avicit.cbb.businessclass.fondsmanage.domain.CbbFondsDbV;
import avicit.cbb.businessclass.fondsmanage.domain.CbbFondsServer;
import avicit.cbb.businessclass.fondsmanage.domain.CbbFondsServerV;
import avicit.cbb.businessclass.fondsmanage.service.CbbFondsDbService;
import avicit.im.common.CommonTreeNode;
import avicit.platform6.commons.utils.PojoUtil;
import avicit.platform6.core.dao.Paging;
import avicit.platform6.core.domain.BeanBase;
import avicit.platform6.core.exception.DaoException;
import avicit.platform6.core.jdbc.JdbcAvicit;
import avicit.platform6.core.jdbc.Page;
import avicit.platform6.core.properties.PlatformConstant.OpResult;
import avicit.platform6.core.properties.PlatformConstant.OpType;
import avicit.platform6.core.service.ServiceBase;
import avicit.platform6.modules.system.syslog.service.LogBaseFactory;
import avicit.platform6.modules.system.syslog.service.SysLogUtil;

/**
 * <p>
 * 金航数码科技有限责任公司
 * </p>
 * <p>
 * 作者：张坚
 * </p>
 * <p>
 * 邮箱：18629436468@163.com
 * </p>
 * <p>
 * 创建时间： 2014-9-24 下午4:16:39
 * </p>
 * <p>
 * 类说明：全宗管理业务接口功能实现类
 * </p>
 * <p>
 * 修改记录：
 * </p>
 */
@Service
public class CbbFondsDbServiceImpl extends ServiceBase implements CbbFondsDbService {

	@Autowired(required = false)
	private JdbcAvicit jdbcAvicit;
	@Autowired(required = true)
	private JdbcTemplate jdbcTemplate;

	@Override
	public void delete(BeanBase arg0) throws DaoException {
		// TODO Auto-generated method stub

	}

	@Override
	public void insert(BeanBase arg0) throws DaoException {
		// TODO Auto-generated method stub

	}

	@Override
	public void update(BeanBase arg0) throws DaoException {
		// TODO Auto-generated method stub

	}

	@Override
	public List<CommonTreeNode> getChildsNode(String id, String deptId, String orgId) throws Exception {
		// 递归一层减一层
		List<CommonTreeNode> nodes = new ArrayList<CommonTreeNode>();
		// 查询业务数据
		List<Map<String, Object>> childTreeList = getCbbFondsDbTreeByParentId(id, deptId, orgId);
		// 将业务数据格式转换成esayui树形格式，此处封装了一个TreeNodeVo节点
		for (Map<String, Object> row : childTreeList) {
			String rid = (String) row.get("ID");
			String rname = "";
			rname = (String) row.get("OD_NAME");
			String orgType = (String) row.get("ORG_DEPT_TYPE");
			BigDecimal childCount2 = BigDecimal.valueOf(Long.valueOf(row.get("CHILD_COUNT") + ""));
			CommonTreeNode node = new CommonTreeNode();
			node.setId(rid);
			node.setText(rname);
			// 根据节点类型,设置节点图标
			if (null != orgType && "1".equals(orgType)) { // 1 一级部门
				node.setIconCls("icon-im-newnorg");
			} else if (null != orgType && "2".equals(orgType)) { // 2 二级部门
				node.setIconCls("icon-im-newnmodulefile");
			}
			node.setAttributes(row);
			if (null != childCount2 && childCount2.longValue() > 0) {
				List<CommonTreeNode> cnodes = getChildsNode(rid, deptId, orgId);
				node.getChildren().addAll(cnodes);
				node.setState("open");
			} else {
				node.setState("open");
			}
			nodes.add(node);
		}
		return nodes;
	}

	/**
	 * 获取子节点
	 * 
	 * @param id
	 * @param deptId
	 * @return
	 */
	public List<Map<String, Object>> getCbbFondsDbTreeByParentId(String parentId, String deptId, String orgId) throws Exception {
		List<Map<String, Object>> list = null;
		try {
			Assert.notNull(parentId, "参数不能为空！");
//			String sql = "SELECT T.*, " + "       (SELECT COUNT(*) FROM CBB_FONDS_TREE_V F WHERE F.OD_PARENT_ID = '" + parentId + "') CHILD_COUNT " + "  FROM CBB_FONDS_TREE_V T "
//					+ " WHERE T.OD_PARENT_ID = '" + parentId + "' AND T.ORG_DEPT_TYPE <> '2' " + "CONNECT BY PRIOR T.ID = T.OD_PARENT_ID " + " START WITH T.OD_PARENT_ID = '" + parentId + "'";
			StringBuffer sbSql = new StringBuffer();
			sbSql.append("SELECT *\n");
			sbSql.append("  FROM (SELECT T.*,\n");
			sbSql.append("               (SELECT COUNT(*)\n");
			sbSql.append("                  FROM CBB_FONDS_TREE_V F\n");
			sbSql.append("                 WHERE F.OD_PARENT_ID = '" + parentId + "') CHILD_COUNT\n");
			sbSql.append("          FROM CBB_FONDS_TREE_V T\n");
			sbSql.append("         WHERE T.OD_PARENT_ID = '" + parentId + "'\n");
			sbSql.append("           AND T.ORG_DEPT_TYPE <> '2'\n");
			sbSql.append("        CONNECT BY PRIOR T.ID = T.OD_PARENT_ID\n");
			sbSql.append("         START WITH T.OD_PARENT_ID = '" + parentId + "')\n");
			sbSql.append(" ORDER BY ORDER_BY"); 
			
			list = this.jdbcTemplate.queryForList(sbSql.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 查询全宗信息
	 */
	public Page<Map<String, Object>> loadCbbFondsDb(Paging<CbbFondsDbV> page, Map<String, Object> parameter) {
		String sysId = (String) parameter.get("sysId");
		String orgId = (String) parameter.get("orgId");
		// String deptId = (String) parameter.get("deptId");
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT * ");
		sbSql.append("  FROM CBB_FONDS_DB_V T ");
		sbSql.append(" WHERE T.ORG_ID = '" + orgId + "' ");
		sbSql.append("   AND T.SYS_ID = '" + sysId + "'");
		// sbSql.append("   AND T.FONDS_DEPT_ID = '" + deptId + "'");
		Page<Map<String, Object>> pageBean = new Page<Map<String, Object>>(page.getPageNo(), page.getPageSize());
		jdbcAvicit.getDatesByPageForMap(pageBean, sbSql.toString(), parameter, null);
		return pageBean;
	}

	/**
	 * 查询全文服务器设置信息
	 */
	public Page<Map<String, Object>> loadCbbFondsServer(Paging<CbbFondsServerV> page, Map<String, Object> parameter) {
		String sysId = (String) parameter.get("sysId");
		String orgId = (String) parameter.get("orgId");
		String cbbFondsDbId = (String) parameter.get("cbbFondsDbId");
		String fullsvrType = (String) parameter.get("fullsvrType");
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT * ");
		sbSql.append("  FROM CBB_FONDS_SERVER_V T ");
		sbSql.append(" WHERE T.ORG_ID = '" + orgId + "' ");
		sbSql.append("   AND T.SYS_ID = '" + sysId + "' ");
		sbSql.append("   AND T.CBB_FONDS_DB_ID = '" + cbbFondsDbId + "' ");
		sbSql.append("   AND T.FULLSVR_TYPE = '" + fullsvrType + "'");
		Page<Map<String, Object>> pageBean = new Page<Map<String, Object>>(page.getPageNo(), page.getPageSize());
		jdbcAvicit.getDatesByPageForMap(pageBean, sbSql.toString(), parameter, null);
		return pageBean;
	}

	/**
	 * 删除全宗信息
	 */
	public void deleteCbbFondsDb(String ids) throws Exception {
		Assert.notNull(ids);
		String[] idArray = ids.split(",");
		for (int i = 0; i < idArray.length; ++i) {
			String sql = "delete from cbb_fonds_db where id = '" + idArray[i] + "'";
			this.jdbcAvicit.getJdbcTemplate().execute(sql);
			SysLogUtil.log("全总管理模块", "删除全宗记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
		}
	}

	/**
	 * 删除全文服务器设置信息
	 */
	public void deleteCbbFondsServer(String ids) {
		Assert.notNull(ids);
		String[] idArray = ids.split(",");
		for (int i = 0; i < idArray.length; ++i) {
			String sql = "delete from cbb_fonds_server where id = '" + idArray[i] + "'";
			this.jdbcAvicit.getJdbcTemplate().execute(sql);
			SysLogUtil.log("全总管理模块", "删除全宗服务器设置记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
		}
	}

	/**
	 * 新增全宗管理记录
	 */
	public void insertCbbFondsDb(CbbFondsDb cbbFondsDb) throws Exception {
		PojoUtil.setSysProperties(cbbFondsDb, OpType.insert);
		this.hibernateDao.save(cbbFondsDb);
		SysLogUtil.log(cbbFondsDb, null, OpType.insert, OpResult.success);
	}

	/**
	 * 获取部门编号、名称信息
	 */
	public Map<String, Object> getSysOrgInfo(String id) {
		String sql = "SELECT ID,OD_CODE,OD_NAME FROM ( " + "SELECT SO.ID, " + "       SO.ORG_CODE AS OD_CODE, " + "       SO.PARENT_ORG_ID AS OD_PARENT_ID, "
				+ "       SOT.ORG_NAME AS OD_NAME, " + "       '1' AS ORG_DEPT_TYPE " + "  FROM SYS_ORG SO " + "  LEFT JOIN SYS_ORG_TL SOT " + "    ON SO.ID = SOT.SYS_ORG_ID "
				+ " WHERE SO.VALID_FLAG = '1' " + "   AND SOT.SYS_LANGUAGE_CODE = 'zh_CN' " + "UNION ALL " + "SELECT SD.ID, " + "       SD.DEPT_CODE AS OD_CODE, "
				+ "       DECODE(SD.PARENT_DEPT_ID,'-1',SD.ORG_ID,SD.PARENT_DEPT_ID) AS OD_PARENT_ID, " + "       SDT.DEPT_NAME AS OD_NAME, " + "        '2' AS ORG_DEPT_TYPE "
				+ "  FROM SYS_DEPT SD " + "  LEFT JOIN SYS_DEPT_TL SDT " + "    ON SD.ID = SDT.SYS_DEPT_ID " + " WHERE SD.VALID_FLAG = '1' "
				+ "   AND SDT.SYS_LANGUAGE_CODE = 'zh_CN') " + "WHERE ID = ?";
		Map<String, Object> map = this.jdbcTemplate.queryForMap(sql, new Object[] {id});
		return map;
	}

	/**
	 * 更新全宗管理记录
	 */
	public void updateCbbFondsDb(CbbFondsDb bean) throws Exception {
		CbbFondsDb objOld = this.hibernateDao.get(CbbFondsDb.class, bean.getId());
		hibernateDao.evict(objOld);
		CbbFondsDb newBean = new CbbFondsDb();
		PojoUtil.copyProperties(newBean, objOld, true);
		PojoUtil.copyProperties(newBean, bean, true);
		SysLogUtil.log(newBean, objOld, OpType.update, OpResult.success);
		PojoUtil.setSysProperties(newBean, OpType.update);
		hibernateDao.update(newBean);
	}

	/**
	 * 加载全宗对象
	 */
	public List<Map<String, Object>> getCbbFondsDbV(String id) {
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT * FROM CBB_FONDS_DB_V T WHERE T.ID = '" + id + "'");
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		list = jdbcAvicit.queryForMap(sbSql.toString(), null);
		return list;
	}

	/**
	 * 新增全宗管理记录
	 */
	public void insertCbbFondsServer(CbbFondsServer cbbFondsServer) throws Exception {
		PojoUtil.setSysProperties(cbbFondsServer, OpType.insert);
		this.hibernateDao.save(cbbFondsServer);
		SysLogUtil.log(cbbFondsServer, null, OpType.insert, OpResult.success);
	}

	/**
	 * 加载全文服务器对象
	 */
	public List<Map<String, Object>> getCbbFondsServerV(String id) {
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("SELECT * FROM CBB_FONDS_SERVER_V T WHERE T.ID = '" + id + "'");
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		list = jdbcAvicit.queryForMap(sbSql.toString(), null);
		return list;
	}

	/**
	 * 更新全文服务器记录
	 */
	public void updateCbbFondsServer(CbbFondsServer bean) throws Exception {
		CbbFondsServer objOld = this.hibernateDao.get(CbbFondsServer.class, bean.getId());
		hibernateDao.evict(objOld);
		CbbFondsServer newBean = new CbbFondsServer();
		PojoUtil.copyProperties(newBean, objOld, true);
		PojoUtil.copyProperties(newBean, bean, true);
		SysLogUtil.log(newBean, objOld, OpType.update, OpResult.success);
		PojoUtil.setSysProperties(newBean, OpType.update);
		hibernateDao.update(newBean);
	}

	/**
	 * 根据全宗记录ID删除全文服务器记录
	 */
	public void deleteCbbFondsServerByDbId(String id) throws Exception {
		String[] idArray = id.split(",");
		for (int i = 0; i < idArray.length; ++i) {
			StringBuffer sbSql = new StringBuffer();
			sbSql.append("delete from cbb_fonds_server where cbb_fonds_db_id = '");
			sbSql.append(id);
			sbSql.append("'");
			this.jdbcAvicit.getJdbcTemplate().execute(sbSql.toString());
			SysLogUtil.log("全总管理模块", "删除全文服务器记录", OpType.delete, OpResult.success, LogBaseFactory.getLogBase());
		}
	}

	@Override
	public boolean saveUniqueValidate(String fullsvrType, String svrAddress, String svrPublishPath, String ftpUser) {
		StringBuffer sbSql = new StringBuffer();
		if ("1".equals(fullsvrType)) {
			// FTP
			sbSql.append("SELECT COUNT(ID) C ");
			sbSql.append("  FROM CBB_FONDS_SERVER T ");
			sbSql.append(" WHERE T.FULLSVR_TYPE = '1' ");
			sbSql.append("   AND T.SVR_ADDRESS = '" + svrAddress + "' ");
			sbSql.append("   AND T.SVR_PUBLISH_PATH = '" + svrPublishPath + "' ");
			sbSql.append("   AND T.FTP_USER = '" + ftpUser + "'");

		} else {
			// WEBSERVICE
			sbSql.append("SELECT COUNT(ID) C ");
			sbSql.append("  FROM CBB_FONDS_SERVER T ");
			sbSql.append(" WHERE T.FULLSVR_TYPE = '2' ");
			sbSql.append("   AND T.SVR_ADDRESS = '" + svrAddress + "' ");
			sbSql.append("   AND T.SVR_PUBLISH_PATH = '" + svrPublishPath + "' ");
		}
		List<Map<String, Object>> list = this.jdbcAvicit.queryForMap(sbSql.toString(), null);
		int c = Integer.parseInt((list.get(0).get("C") + ""));
		if (c > 0) {
			return true;
		}
		return false;
	}

	@Override
	public boolean editUniqueValidate(String id, String fullsvrType, String svrAddress, String svrPublishPath, String ftpUser) {
		StringBuffer sbSql = new StringBuffer();
		if ("1".equals(fullsvrType)) {
			// FTP
			sbSql.append("SELECT COUNT(ID) C ");
			sbSql.append("  FROM CBB_FONDS_SERVER T ");
			sbSql.append(" WHERE T.FULLSVR_TYPE = '1' ");
			sbSql.append("   AND T.SVR_ADDRESS = '" + svrAddress + "' ");
			sbSql.append("   AND T.SVR_PUBLISH_PATH = '" + svrPublishPath + "' ");
			sbSql.append("   AND T.FTP_USER = '" + ftpUser + "'");
			sbSql.append("   AND T.ID != '" + id + "'");

		} else {
			// WEBSERVICE
			sbSql.append("SELECT COUNT(ID) C ");
			sbSql.append("  FROM CBB_FONDS_SERVER T ");
			sbSql.append(" WHERE T.FULLSVR_TYPE = '2' ");
			sbSql.append("   AND T.SVR_ADDRESS = '" + svrAddress + "' ");
			sbSql.append("   AND T.SVR_PUBLISH_PATH = '" + svrPublishPath + "' ");
			sbSql.append("   AND T.ID != '" + id + "'");
		}
		List<Map<String, Object>> list = this.jdbcAvicit.queryForMap(sbSql.toString(), null);
		int c = Integer.parseInt((list.get(0).get("C") + ""));
		if (c > 0) {
			return true;
		}
		return false;
	}
	
	/**
	 * 获取全宗单位数据库连接信息
	 * @return
	 * @throws Exception List<Map<String,Object>>
	 */
	@SuppressWarnings("unused")
	public List<Map<String, Object>> getCbbFondsDbInfoStr(Map<String,Object> paramMap) throws Exception {
		String paramSysId = (String)paramMap.get("sysId");
		List<Map<String, Object>> listConn = new ArrayList<Map<String, Object>>();
		String sql = "SELECT * FROM CBB_FONDS_DB_V T WHERE T.SYS_ID = '"+paramSysId+"'";
		List<Map<String, Object>> list = this.jdbcAvicit.getJdbcTemplate().queryForList(sql);
		if (list != null && list.size() > 0){
			String dbConnectionStr = "";//返回的连接字符串
			String dbType = JProperties.getFileProperties("dbType");//数据库部署方式 1：集群 2：单实例

			//String orgId = "";
			String sysId = "";
			String ip = "";// IP
			String port = "";// 端口
			String dbName = "";// 库名
			String dbUser = "";// 用户名
			String dbPwd = "";// 密码
			String databaseCate = "oracle";
			String driverClassName = "oracle.jdbc.driver.OracleDriver";
			
			int maxConnPer = 30; // 每个分区中的最大连接数
			int minConnPer = 10; // 每个分区中的最小连接数
			int partitionCount = 1; //分区数
			long idleMaxAge = 3; //连接空闲时间(分钟)
			int acquireIncrement = 5; //连接耗尽时BoneCP一次同时获取的连接数
			int poolAvailabilityThreshold = 2; //设置连接池阀值
			long connectionTimeout = 300000;//获取connection超时的时间
			long idleConnectionTestPeriod = 1; //设置测试connection的间隔时间(分钟)
			
			String databaseCateTemp = JProperties.getFileProperties("databaseCate");
			String driverClassNameTemp = JProperties.getFileProperties("driverClassName");
			String maxConnPerTemp = JProperties.getFileProperties("maxConnectionsPerPartition");
			String minConnPerTemp = JProperties.getFileProperties("minConnectionsPerPartition");
			String partitionCountTemp = JProperties.getFileProperties("partitionCount");
			String idleMaxAgeTemp = JProperties.getFileProperties("idleMaxAge");
			String acquireIncrementTemp = JProperties.getFileProperties("acquireIncrement");
			String poolAvailabilityThresholdTemp = JProperties.getFileProperties("poolAvailabilityThreshold");
			String connectionTimeoutTemp = JProperties.getFileProperties("connectionTimeout");
			String idleConnectionTestPeriodTemp = JProperties.getFileProperties("idleConnectionTestPeriod");
			
			if (databaseCateTemp != null && !"".equals(databaseCateTemp)){
				databaseCate = databaseCateTemp;
			}
			
			if (driverClassNameTemp != null && !"".equals(driverClassNameTemp)){
				driverClassName = driverClassNameTemp;
			}
			
			if (maxConnPerTemp != null && !"".equals(maxConnPerTemp)){
				maxConnPer = Integer.valueOf(maxConnPerTemp);
			}
			
			if (minConnPerTemp != null && !"".equals(minConnPerTemp)){
				minConnPer = Integer.valueOf(minConnPerTemp);
			}
			
			if (partitionCountTemp != null && !"".equals(partitionCountTemp)){
				partitionCount = Integer.valueOf(partitionCountTemp);
			}
			
			if (idleMaxAgeTemp != null && !"".equals(idleMaxAgeTemp)){
				idleMaxAge = Long.valueOf(idleMaxAgeTemp);
			}
			
			if (acquireIncrementTemp != null && !"".equals(acquireIncrementTemp)){
				acquireIncrement = Integer.valueOf(acquireIncrementTemp);
			}
			
			if (poolAvailabilityThresholdTemp != null && !"".equals(poolAvailabilityThresholdTemp)){
				poolAvailabilityThreshold = Integer.valueOf(poolAvailabilityThresholdTemp);
			}
			
			if (connectionTimeoutTemp != null && !"".equals(connectionTimeoutTemp)){
				connectionTimeout = Long.valueOf(connectionTimeoutTemp);
			}
			
			if (idleConnectionTestPeriodTemp != null && !"".equals(idleConnectionTestPeriodTemp)){
				idleConnectionTestPeriod = Long.valueOf(idleConnectionTestPeriodTemp);
			}
			
			for (int i = 0; i < list.size();i++) {
				Map<String, Object> map = new HashMap<String, Object>();
				Map<String, Object> dbMap = list.get(i);
				//orgId = String.valueOf(dbMap.get("ORG_ID"));
				sysId = String.valueOf(dbMap.get("SYS_ID"));
				ip = String.valueOf(dbMap.get("DB_IP"));
				port = String.valueOf(dbMap.get("DB_PORT"));
				dbName = String.valueOf(dbMap.get("DB_NAME"));
				dbUser = String.valueOf(dbMap.get("DB_USER"));
				dbPwd = String.valueOf(dbMap.get("DB_PASSWORD"));
				if ("oracle".equals(databaseCate)){
					if(null != dbType && !"".equals(dbType) && "1".equals(dbType)){
						dbConnectionStr = "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = "+ ip +")(PORT = " + port + "))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = " + dbName + ")))";
					}else{
						dbConnectionStr = ip + ":" + port + ":" + dbName;
					}
					dbConnectionStr = "jdbc:oracle:thin:@"+dbConnectionStr;
				} else {
					//预留
				}
				
				map.put("databaseType",databaseCate);
				map.put("cpName", sysId);
				map.put("connDriver", driverClassName);
				map.put("connStr", dbConnectionStr);
				map.put("dbUser", dbUser);
				map.put("dbPwd", dbPwd);
				map.put("ipStr", ip);
				
				map.put("maxConnPer", maxConnPer);
				map.put("minConnPer", minConnPer);
				map.put("partitionCount", partitionCount);
				map.put("idleMaxAge", idleMaxAge);
				map.put("acquireIncrement", acquireIncrement);
				map.put("poolAvailabilityThreshold", poolAvailabilityThreshold);
				map.put("connectionTimeout", connectionTimeout);
				map.put("idleConnectionTestPeriod", idleConnectionTestPeriod);
				listConn.add(map);
				//暂时获取其中一个，退出循环
				break;
			}
		}
		return listConn;
	}
}
